package com.cms.biz.queryAnalyse.query;

import java.sql.ResultSet;
import java.sql.Statement;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.stereotype.Repository;

import com.common.util.EMPSQLUtil;
import com.efounder.builder.base.data.DataSetUtils;
import com.efounder.builder.base.data.EFDataSet;
import com.efounder.builder.base.data.EFRowSet;
import com.efounder.eai.data.JParamObject;
import com.efounder.eai.data.JResponseObject;
import com.efounder.sql.JConnection;
import com.efounder.sql.JStatement;
import com.enums.EMPQueryParamEnum;
import com.mrp.persistence.queryAnalyse.pageQuery.bean.EMPPageQuery;
import com.pub.util.EMPLimitConvertUtil;

/**
 * <p>Title: 项目查询分析</p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2010</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
@Repository
public class EMPPaidContractInfoQuery {

	@Autowired
	private JdbcTemplate                  jdbcTemplate;
	
	protected     String                   msTableName = "";
	
	protected     String                  msTableName1 = "";
	
	public JResponseObject QueryObject(JParamObject PO){
		JConnection         conn = null;
		JStatement          stmt = null;
		EFDataSet   queryDataset = null;
		EFRowSet     queryRowset = EFRowSet.getInstance();
		JResponseObject       RO = null;
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			conn.setAutoCommit(true);
			stmt = conn.createStatement();
			
			// 获取查询数据
			queryDataset = convertDataSet(stmt, PO);
			queryRowset.setExtProperty(EMPQueryParamEnum.PAGEQUERY, PO.getValue(EMPQueryParamEnum.PAGEQUERY, null));
			queryRowset.setDataSet(EMPQueryParamEnum.QUERYRESULT, queryDataset);
			// 封装返回数据
		   	RO = new JResponseObject(queryRowset, 0);
		} catch(Exception ce) {
			ce.printStackTrace();
			RO = new JResponseObject(null, -1, ce);
		} finally {
			EMPSQLUtil.closeAllResources(null, stmt, conn);
		}
		return RO;
	}
	
	/**
	 * 组织数据的过程
	 * @throws Exception
	 * @return TreeTableDataManager
	 */
	public EFDataSet convertDataSet(Statement stmt, JParamObject po) throws Exception {
		EFDataSet queryDataset = null;
		EFDataSet   newDataset = EFDataSet.getInstance();
		String          F_GUID = po.GetValueByParamName("F_GUID", "");
		String          F_HTBH = "";
		ResultSet           rs = null;
		String          strSql = ""; 
		EMPPageQuery pageQuery = (EMPPageQuery) po.getValue(EMPQueryParamEnum.PAGEQUERY, "");

		strSql = " select * from HTXXZD where F_GUID = '" + F_GUID + "'";
		rs = stmt.executeQuery(strSql);
		
		if(rs != null && rs.next()) {
			F_HTBH = rs.getString("F_HTBH");
		}
		strSql = " select HTFKXX.F_BZ as F_BZ,HTFKXX.F_CHDATE as F_CHDATE,HTFKXX.F_CRDATE as F_CRDATE,HTFKXX.F_DJBH as F_DJBH," 
			   + " HTFKXX.F_DJZT as F_DJZT,HTFKXX.F_FKCS as F_FKCS,HTFKXX.F_FKJE as F_FKJE,HTFKXX.F_GUID as F_GUID,HTFKXX.F_HTBH as F_HTBH," 
			   + "F_HTBH.F_HTMC as F_HTBHMC,HTFKXX.F_KJQJ as F_KJQJ,HTFKXX.F_TJR as F_TJR,F_TJR.USER_NAME as F_TJRMC," 
			   + "HTFKXX.F_TJRQ as F_TJRQ,HTFKXX.F_ZDR as F_ZDR,F_ZDR.USER_NAME as F_ZDRMC,HTFKXX.F_ZDRQ as F_ZDRQ " 
			   + "from HTFKXX  " 
			   + "LEFT JOIN HTXXZD F_HTBH ON F_HTBH.F_HTBH = HTFKXX.F_HTBH " 
			   + "LEFT JOIN BSUSER F_TJR ON F_TJR.USER_ID = HTFKXX.F_TJR " 
			   + "LEFT JOIN BSUSER F_ZDR ON F_ZDR.USER_ID = HTFKXX.F_ZDR " 
			   + "where HTFKXX.F_HTBH = '" + F_HTBH + "' and F_DJZT = '1' order by HTFKXX.F_KJQJ, HTFKXX.F_DJBH";
		rs = stmt.executeQuery(strSql);
		
		queryDataset = DataSetUtils.resultSet2DataSet(rs, queryDataset);
		
		if(pageQuery == null) return queryDataset;
		
		pageQuery.setTotalCount(queryDataset.getRowCount());
		newDataset = EMPLimitConvertUtil.getDataSetLimit(queryDataset, pageQuery.getPageNum(), pageQuery.getNumPerPage());
		return newDataset;
	}
}
